import re
import pandas as pd
from pypinyin import pinyin, Style


def hanzi_to_initials(s):
    """将汉字转换为拼音首字母并大写"""
    return ''.join([p[0][0].upper() for p in pinyin(s, style=Style.FIRST_LETTER)])


def convert_part(text):
    """转换字符串中的汉字部分，非汉字保留"""
    converted = []
    current_hanzi = []
    for char in text:
        if '\u4e00' <= char <= '\u9fff':  # 判断是否为汉字
            current_hanzi.append(char)
        else:
            if current_hanzi:
                converted.append(hanzi_to_initials(''.join(current_hanzi)))
                current_hanzi = []
            converted.append(char)
    if current_hanzi:
        converted.append(hanzi_to_initials(''.join(current_hanzi)))
    return ''.join(converted)


def process_room_name(room_name):
    """处理room_name生成新的room_code"""
    parts = room_name.split('-', 1)
    if len(parts) < 2:
        return convert_part(room_name)

    prefix, rest = parts[0].strip(), parts[1].strip()
    bracket_match = re.search(r'\((.*?)\)', rest)

    main_part = rest.split('(', 1)[0].strip() if bracket_match else rest
    bracket_content = bracket_match.group(1) if bracket_match else None

    code_prefix = convert_part(prefix)
    code_main = convert_part(main_part)
    code_bracket = f'({convert_part(bracket_content)})' if bracket_content else ''

    return f"{code_prefix}.{code_main}{code_bracket}"

# 定义函数来格式化id
def format_id(id_value):
    # 只有在id_value不为空时才进行处理
    if pd.isna(id_value) or id_value == "":
        return id_value  # 返回原值
    # 将id补齐为24位，前四位为6321，中间补0，剩余位置用原id填充
    return f"6321{'0' * (20 - len(str(id_value)))}{id_value}"

# 读取.xls文件
df = pd.read_excel('./data/room.xls',dtype={'id':str,'org_id':str,'building_id':str})

# 应用函数格式化id列
df['id'] = df['id'].apply(format_id)
# 应用函数格式化org_id列
df['org_id'] = df['org_id'].apply(format_id)
# 应用函数格式化building_id列
df['building_id'] = df['building_id'].apply(format_id)
# 处理room_name列，生成新的room_code
df['room_code'] = df['room_name'].apply(process_room_name)

# 保存为.xls文件
df.to_excel('C:/room.xls', index=False,engine="openpyxl")
print("数据格式化完成！")